﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.Linq;
using System.Text;

namespace ALDIOrganizer
{
    public static partial class SQLiteAccess
    {
        /// <summary>
        /// Deletes an employee and all the child records from the database
        /// </summary>
        /// <param name="pEmployeeID">
        /// Employee's ID (primary key value)
        /// </param>
        /// <remarks>
        /// 1.  Do not use when the application UI is databound to records!
        ///     You can call it before the main form is initialized.
        /// 2.  It's generally not meant to be used, and as of now, it isn't used
        ///     throughout the application (there is no "delete employee" button anywhere).
        ///     
        ///     The idea is that no employee should be deleted from the database with no trace.
        ///     If they leave the place - you just update the data in their job contract
        ///     history, but it doesn't change the fact that they were once employed.
        ///     No person can disappear into thin air like in some sort of an Orwellian dystopia.
        ///     If there was an input mistake (eg. a typo in somebody's name) - updating the record
        ///     is enough.        
        /// </remarks>
        internal static void DeleteEmployee(Int64 pEmployeeID)
        {
            var _employee = GetEmployee(pEmployeeID);
            var _children = new List<DataRow>();
            var builder = new SQLiteCommandBuilder(_adapter);
            using (var delete = new SQLiteCommand(_connection)
                                    {
                                         CommandText = @"DELETE FROM employees WHERE id = " + pEmployeeID.ToString() + "; " + @"DELETE FROM contracts WHERE employeeID = " + pEmployeeID.ToString() + "; " + @"DELETE FROM holidays WHERE employeeID = " + pEmployeeID.ToString() + "; " + @"DELETE FROM requests WHERE employeeID = " + pEmployeeID.ToString() + "; "
                                    })
            {
                _adapter.DeleteCommand = delete;
            }

            #region marking child rows for deletion            
            for (int _table = 1; _table < 4; _table++)
            {
                foreach (var _row in from _record in _dataset.Tables[_table].AsEnumerable()
                                     where _record.Field<Int64>("employeeID") == pEmployeeID
                                     select _record)
                {
                    _children.Add(_row);
                }
            }
            _employee.Delete();
            foreach (var _child in _children)
            {
                _child.Delete();
            }
            #endregion
            
            // updating the source CurrentFile
            _adapter.Update(_dataset.GetChanges(DataRowState.Deleted));

            #region removing rows from the Dataset            
            _employee.AcceptChanges();
            foreach (var child in _children)
            {
                child.AcceptChanges();
            }
            #endregion            
        }

        /// <summary>
        /// Deletes specified contract from Contracts data table
        /// </summary>
        /// <param name="pContractID">ID (primary key) of the contract to be deleted</param>        
        internal static void DeleteContract(Int64 pContractID)
        {
            var contractRow = GetContract(pContractID);
            if (contractRow == null)
            {
                // Debugger.Break();
                return;
            }
            var employeeID = new Contract(contractRow).empID;
            var date = new Contract(contractRow).date;            
            var builder = new SQLiteCommandBuilder(_adapter);            
            var delete = new SQLiteCommand(_connection)
                              {
                                  CommandText = @"DELETE FROM contracts " +
                                                @"WHERE id = " +
                                                pContractID.ToString()
                              };
            _adapter.DeleteCommand = delete;
            contractRow.Delete();            
            _adapter.Update(_dataset.Tables[1].GetChanges(DataRowState.Deleted));
            contractRow.AcceptChanges();

            OnDataChanged(
                employeeID,
                new [] { 0, 1 },
                (DateTime?)date,
                (DateTime?)null);        
        }

        /// <summary>
        /// Deletes specified holiday from Holidays datatable
        /// </summary>
        /// <param name="pHolidayID">ID (primary key) of the holiday to be deleted</param>        
        internal static void DeleteHoliday(Int64 pHolidayID)
        {
            var holiday = GetHolidays(pHolidayID);
            if (holiday == null)
            {                
                return;
            }
            var employeeID = new Holiday(holiday).empID;
            var startDate = new Holiday(holiday).startDate;
            var endDate = new Holiday(holiday).endDate;            
            var builder = new SQLiteCommandBuilder(_adapter);
            var delete = new SQLiteCommand(_connection)
                              {
                                  CommandText = @"DELETE FROM holidays " +
                                                @"WHERE id = " +
                                                pHolidayID.ToString()
                              };
            _adapter.DeleteCommand = delete;
            holiday.Delete();
            _adapter.Update(_dataset.Tables[2].GetChanges(DataRowState.Deleted));
            holiday.AcceptChanges();

            OnDataChanged(
                employeeID,
                new [] { 0, 2 },
                (DateTime?)startDate,
                (DateTime?)endDate);            
        }

        /// <summary>
        /// Deletes specified request from Requests datatable
        /// </summary>
        /// <param name="pRequestID">ID (primary key) of the request to be deleted</param>        
        internal static void DeleteRequest(Int64 pRequestID)
        {
            var request = GetRequest(pRequestID);
            if (request == null)
            {
                return;
            }
            var employeeId = new Request(request).empID;
            var startDate = new Request(request).startDate;
            var endDate = new Request(request).endDate;
                       
            var builder = new SQLiteCommandBuilder(_adapter);
            var delete = new SQLiteCommand(_connection)
                             {
                                 CommandText = @"DELETE FROM requests " +
                                               @"WHERE id = " +
                                               pRequestID
                             };
            _adapter.DeleteCommand = delete;
            request.Delete();
            _adapter.Update(_dataset.Tables[3].GetChanges(DataRowState.Deleted));
            request.AcceptChanges();
                        
            if (OnDataChanged != null)
            {
                OnDataChanged(
                    employeeId,
                    new [] { 0, 3 },
                    (DateTime?)startDate,
                    endDate);
            }            
        }

        /// <summary>
        /// Deletes requests that no longer apply from the Requests datatable
        /// </summary>
        /// <remarks>
        /// SweepExpiredRequestsOnlyPending setting (true by default) specifies
        /// whether only pending requests are removed.
        /// </remarks>
        internal static void DeleteOutdatedRequests()
        {            
            var expired = (from request in
                               (from row in _dataset.Tables[3].AsEnumerable()
                                select new Request(row))
                            where (request.endDate < DateTime.Now)                            
                            select request).ToArray();
            
            foreach (var request in expired)
            {
                if (Settings.Default.SweepExpiredRequestsOnlyPending &&
                    !request.Pending)
                {
                    continue;
                }                
                DeleteRequest(request.ID);
            }
        }

        internal static void DeleteOutdatedPendingHolidays()
        {
            var outdated = (from holiday in
                                (from row in _dataset.Tables[2].AsEnumerable()
                                 select new Holiday(row))
                            where holiday.startDate < DateTime.Now &&
                                  holiday.pending
                            select holiday).ToArray();
            foreach(var holiday in outdated)
            {
                DeleteHoliday(holiday.ID);
            }
        }
    }
}
